package batch.pmgm;

import java.sql.*;
import java.util.*;
import java.util.Date;
import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.log4j.*;

import batch.common.db.*;
import batch.common.report.*;

public class PmgmSt_tot {
    private final String TODAY  = DateFormatUtils.format(new Date(), "yyyyMMdd");
    
    private final String MAILSUBJECT = "PmgmSt_tot Error! (" + TODAY + ")"; 

    private String batch_date;

    /*******************************************************************************************************/
    /* Name : PmgmSt_tot
    /* Arguments : none
    /* Return : none
    /* Description : creator
    /*******************************************************************************************************/
    public PmgmSt_tot(){
        batch_date = TODAY;
    }

    /*******************************************************************************************************/
    /* Name : PmgmSt_tot
    /* Arguments : String(--yyyymmdd--)
    /* Return : none
    /* Description : creator
    /*******************************************************************************************************/
    public PmgmSt_tot(String date){
        batch_date = date;
    }
    
    /*******************************************************************************************************/
    /* Name : main
    /* Arguments : String(--yyyymmdd--)
    /*******************************************************************************************************/
    public static void main(String[] args)
    {

        PmgmSt_tot pmgm_tot = null;

        if ( args==null || args.length==0 ) {
            pmgm_tot = new PmgmSt_tot();
        } else {
            pmgm_tot = new PmgmSt_tot(args[0]);
        }

        Logger.getLogger("pmgm.pmgmst_tot").info(" ( * ) Start.");
        pmgm_tot.start();
        Logger.getLogger("pmgm.pmgmst_tot").info(" ( * ) End.");
    }
    /*******************************************************************************************************/

    /*******************************************************************************************************/
    /* Name : start
    /* Arguments : none
    /* Return : none
    /* Description : execute each statistics function
    /*******************************************************************************************************/
    public void start()
    {
        Connection conn = null;
        int nCnt = 0;

        try {
            // get connection
            conn = ConnectionManager.getConnection();
            conn.setAutoCommit(false);

            // get promotion media list to be deleted.
            Logger.getLogger("pmgm.pmgmst_tot").info(" ( * ) select statistics of subscribers start.");
            Vector vPromo_list = selectDelPromotion(conn);
            Logger.getLogger("pmgm.pmgmst_tot").info(" ( * ) success. " + vPromo_list.size() + " selected.");

            // update or insert G_PMGM_ST_TOT
            if (vPromo_list.size() != 0) {
                Logger.getLogger("pmgm.pmgmst_tot").info(" ( * ) insert total statistics start.");
                updatePmgm_tot(vPromo_list, conn);
                Logger.getLogger("pmgm.pmgmst_tot").info(" ( * ) success.");
            }

            // final commit
            conn.commit();
        } catch(Exception e) {
            if (conn != null) try { conn.rollback(); } catch (Exception ex) { ex.printStackTrace(); }
            
            e.printStackTrace();

            Logger.getLogger("pmgm.pmgmst_tot").info(" ( ! ) " + e.toString());

            StackTraceElement[] stElement = e.getStackTrace();
            for(int p=0; p<stElement.length;p++){
                Logger.getLogger("pmgm.pmgmst_tot").info(" ( ! ) " + stElement[p].toString());
            }

            ReportError.byEmail(MAILSUBJECT, e.toString());
        } finally {
            if (conn != null) try { conn.close(); } catch (Exception e) { e.printStackTrace(); }
        }
    }

    /*******************************************************************************************************/
    /* Name : selectDelPromotion
    /* Arguments : Connection
    /* Return : Vector vPromo_list (--table name array--)
    /* Description : select media list which registration occurred with.
    /*******************************************************************************************************/
    public Vector selectDelPromotion(Connection conn) throws Exception {
        Vector vPromo_list = new Vector();
        StringBuffer sql = new StringBuffer();
        sql.append( "SELECT B.MST_CD, B.SITE_CD, B.SUB_CD, A.HP_BRAND, A.GRADE, A.NEWS_LETTER,");
        sql.append(       " GET_PMGM_AGE(TO_CHAR(A.CREATED,'YYYYMMDD'),A.BIRTH), DECODE(A.GENDER,'F',A.GENDER,'M',A.GENDER,'N') GENDER, COUNT(*)");
        sql.append(" FROM G_MB_MEMBER A, G_PMGM_MST_SUB B");
        sql.append(" WHERE A.MEMBERGBN=B.MST_CD || B.SUB_CD");
        sql.append(" AND A.CREATED >= TO_DATE('" + batch_date + "','YYYYMMDD')");
        sql.append(" AND A.CREATED < TO_DATE('" + batch_date + "','YYYYMMDD')+1");
        sql.append(" AND A.REG_COUNTRY = B.SITE_CD");
        sql.append(" GROUP BY B.MST_CD, B.SITE_CD, B.SUB_CD, A.HP_BRAND, A.GRADE, A.NEWS_LETTER,");
        sql.append(         " GET_PMGM_AGE(TO_CHAR(A.CREATED,'YYYYMMDD'),A.BIRTH), DECODE(A.GENDER,'F',A.GENDER,'M',A.GENDER,'N')");

        Statement stmt = null;
        ResultSet rs = null;

        try{
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql.toString());
            while(rs.next()){
                String[] arrList = new String[9];
                arrList[0] = rs.getString(1);
                arrList[1] = rs.getString(2);
                arrList[2] = rs.getString(3);
                arrList[3] = rs.getString(4);
                arrList[4] = rs.getString(5);
                arrList[5] = rs.getString(6);
                arrList[6] = rs.getString(7);
                arrList[7] = rs.getString(8);
                arrList[8] = rs.getString(9);
                vPromo_list.addElement(arrList);
            }
        }
        finally {
            if (rs != null) try { rs.close(); } catch (Exception e) {}
            if (stmt != null) try { stmt.close(); } catch (Exception e) {}
        }

        return vPromo_list;
    }

    /*******************************************************************************************************/
    /* Name : updatePmgm_tot
    /* Arguments : Vector vPromo_list, Connection
    /* Return : void
    /* Description : G_PMGM_ST_TOT
    /*******************************************************************************************************/
    public void updatePmgm_tot(Vector vPromo_list, Connection conn) throws Exception {
        String sUpdateSQL = "UPDATE G_PMGM_ST_TOT SET CNT_NUM=CNT_NUM+? WHERE MST_CD=? AND SITE_CD=? AND SUB_CD=? AND HP_BRAND=? AND GRADE_CD=? AND OPT_CD=? AND AGE_CD=? AND GENDER_CD=?";
        String sInsertSQL = "INSERT INTO G_PMGM_ST_TOT VALUES (SEQ_G_PMGM_ST_MB.NEXTVAL, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        PreparedStatement pstmtUpdate = null;
        PreparedStatement pstmtInsert = null;
        int nCnt = 0;

        try {
            pstmtUpdate = conn.prepareStatement(sUpdateSQL);
            pstmtInsert = conn.prepareStatement(sInsertSQL);

            for (int i=0 ; i<vPromo_list.size() ; i++) {
                String[] arrList = (String[])vPromo_list.elementAt(i);
                pstmtUpdate.setInt(1, Integer.parseInt(arrList[8]));
                pstmtUpdate.setString(2, arrList[0]);
                pstmtUpdate.setString(3, arrList[1]);
                pstmtUpdate.setString(4, arrList[2]);
                pstmtUpdate.setString(5, arrList[3]);
                pstmtUpdate.setString(6, arrList[4]);
                pstmtUpdate.setString(7, arrList[5]);
                pstmtUpdate.setString(8, arrList[6]);
                pstmtUpdate.setString(9, arrList[7]);
                nCnt = pstmtUpdate.executeUpdate();
                if (nCnt==1) {
                    Logger.getLogger("pmgm.pmgmst_tot").info(" ( * ) ["+arrList[0]+"|"+arrList[1]+"|"+arrList[2]+"|"+arrList[3]+"|"+arrList[4]+"|"+arrList[5]+"|"+arrList[6]+"|"+arrList[7]+"|"+arrList[8]+"] updated.\n");
                }
                else {
                    if (nCnt!=0) throw new Exception("Rows over one are updated");
                    pstmtInsert.setString(1, arrList[0]);
                    pstmtInsert.setString(2, arrList[1]);
                    pstmtInsert.setString(3, arrList[2]);
                    pstmtInsert.setString(4, arrList[3]);
                    pstmtInsert.setString(5, arrList[4]);
                    pstmtInsert.setString(6, arrList[5]);
                    pstmtInsert.setString(7, arrList[6]);
                    pstmtInsert.setString(8, arrList[7]);
                    pstmtInsert.setInt(9, Integer.parseInt(arrList[8]));
                    nCnt = pstmtInsert.executeUpdate();
                    Logger.getLogger("pmgm.pmgmst_tot").info(" ( * ) ["+arrList[0]+"|"+arrList[1]+"|"+arrList[2]+"|"+arrList[3]+"|"+arrList[4]+"|"+arrList[5]+"|"+arrList[6]+"|"+arrList[7]+"|"+arrList[8]+"] inserted.\n");
                }
            }
        }
        finally {
            if (pstmtUpdate!=null) try { pstmtUpdate.close(); } catch (Exception e) {}
            if (pstmtInsert!=null) try { pstmtInsert.close(); } catch (Exception e) {}
        }
    }
}
